package zuoye;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;

public class HomeWrork {

	private Connection conn = java_35_jdbc.JDBCUtil.getConnection();

	@Test
	public void homeWork1() throws SQLException {
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("select deptno,count(*)from emp group by deptno");
		while (rs.next()) {
			System.out.println(rs.getInt(1) + "\t" + rs.getInt(2));
		}
		stat.close();
	}

	@Test
	public void homeWork2() throws SQLException {
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(
				"select dname,t.tc from dept,( select deptno td,count(*) tc from emp group by deptno  having count(*) > 3) t where dept.deptno = t.td");
		while (rs.next()) {
			System.out.println(rs.getString(1) + "\t" + rs.getString(2));
		}
		stat.close();
	}

	@Test
	public void homeWork3() throws SQLException {
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("select ename from emp where sal > (select sal from emp where ename='JONES')");
		while (rs.next()) {
			System.out.println(rs.getString(1));
		}
		stat.close();
	}

	@Test
	public void homeWork4() throws SQLException {
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno");
		while (rs.next()) {
			System.out.println(rs.getString(1) + "\t" + rs.getString(2));
		}
		stat.close();
	}

	@Test
	public void homeWork5() throws SQLException {
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(
				"select * from (select job,avg(sal) from emp group by job order by avg(sal) desc) where rownum <=2");
		while (rs.next()) {
			System.out.println(rs.getString(1) + "\t" + rs.getInt(2));
		}
		stat.close();
	}

	@Test
	public void hm6() throws SQLException {
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(
				"select ename,deptno from emp where deptno != 20 and sal > ALL(select sal from emp where deptno = 20)");
		while (rs.next()) {
			System.out.println(rs.getString(1));
		}
	}

	@Test
	public void hm7() throws SQLException {
		PreparedStatement pstat = conn.prepareStatement("select job from emp group by job having avg(sal) > ?");
		pstat.setInt(1, 2000);
		ResultSet rs = pstat.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getString(1));
		}
	}

	@Test
	public void hm8() throws SQLException {
		PreparedStatement pstat = conn
				.prepareStatement("select deptno,avg(sal) from emp where sal > ? group by deptno having avg(sal) > ?");
		pstat.setInt(1, 2000);
		pstat.setInt(2, 2500);
		ResultSet rs = pstat.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getInt(1) + "," + rs.getInt(2));
		}
	}
	
	@Test
	public void hm9() throws SQLException {
		PreparedStatement pstat = conn.prepareStatement(
				"select deptno,dname,loc from dept where deptno =(select * from (select deptno from emp group by deptno order by sum(sal)) where rownum = 1)");
		ResultSet rs = pstat.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getString(3));
		}
	}

	@Test
	public void hm10() throws SQLException {
		PreparedStatement pstat = conn.prepareStatement(
				"select deptno from emp group by deptno having avg(sal) between (select losal from salgrade where grade = ?) and (select hisal from salgrade where grade = ?)");
		pstat.setInt(1, 3);
		pstat.setInt(2, 3);
		ResultSet rs = pstat.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getInt(1));
		}
	}

	@Test
	public void hm11() throws SQLException {
		PreparedStatement pstat = conn.prepareStatement(
				"select ename,dname,loc from (select rownum r,ename,dname,loc from (select ename,dname,loc from emp,dept where emp.deptno = dept.deptno and emp.deptno in(10,20) order by sal desc)) where r between ? and ?");
		pstat.setInt(1, 3);
		pstat.setInt(2, 5);
		ResultSet rs = pstat.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3));
		}
	}

	@Test
	public void hm12() throws SQLException {
		PreparedStatement pstat = conn.prepareStatement(
				"select e1.empno,e1.ename,e1.sal+nvl(e1.comm,0) from emp e1,emp e2  where e1.mgr = e2.empno and (e1.sal+nvl(e1.comm,0)) > (e2.sal+nvl(e2.comm,0)) ");
		ResultSet rs = pstat.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt(3));
		}
	}

	@Test
	public void hm13() throws SQLException {
		CallableStatement cs = conn.prepareCall("{call pro_13(?,?)}");
		cs.setInt(1, 4);
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet) cs.getObject(2);
		while (rs.next()) {
			System.out.println(rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3));
		}
		rs.close();
		cs.close();
	}

	@Test
	public void hm14() throws SQLException {
		CallableStatement cs = conn.prepareCall("{call pro_14(?,?,?)}");
		cs.setString(1, "MARTIN");
		cs.setString(2, "MSMITH");
		cs.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet) cs.getObject(3);
		while (rs.next()) {
			System.out.println(rs.getInt(1));
		}
		rs.close();
		cs.close();
	}

	@Test
	public void hm15() throws SQLException {
		CallableStatement cs = conn.prepareCall("{call pro_15(?)}");
		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet) cs.getObject(1);
		while (rs.next()) {
			System.out.println(rs.getString(1));
		}
		rs.close();
		cs.close();
	}

	@Test
	public void hm16() throws SQLException {
		CallableStatement cs = conn.prepareCall("{call pro_16(?)}");
		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet) cs.getObject(1);
		while (rs.next()) {
			System.out.println(rs.getString(1) + "," + rs.getString(2));
		}
		rs.close();
		cs.close();
	}

	@Test
	public void hm17() throws SQLException {
		CallableStatement cs = conn.prepareCall("{call pro_17(?,?)}");
		cs.setString(1, "KING");
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet) cs.getObject(2);
		while (rs.next()) {
			System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt(3));
		}
		rs.close();
		cs.close();
	}

	@Test
	public void hm18() throws SQLException {
		CallableStatement cs = conn.prepareCall("{call pro_17(?,?)}");
		cs.setString(1, "KING");
		cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		ResultSet rs = (ResultSet) cs.getObject(2);
		while (rs.next()) {
			System.out.println(rs.getString(1));
		}
		rs.close();
		cs.close();
	}
}
